﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace OreSystem.Common
{
    public static class MsSqlHelper
    {
        public static readonly string connStr = ConfigurationManager.ConnectionStrings["mssql"].ConnectionString;
        /// <summary>
        /// 执行一个insert update delete 操作。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static int ExceuteNoQuery(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.Add(pms);
                    }
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        public static int ExceuteNoQueryByConnStr(string sql,string connStr, params SqlParameter[] pms)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.Add(pms);
                    }
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
               
            }
        }

        public static int ExceuteNoQuery(string sql)
        {
            return MsSqlHelper.ExceuteNoQuery(sql, null);
        }
        //测试连接字符串能否连接
        public static string OpenConnection(string connStr)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connStr;
            try
            {
                conn.Open();
                return conn.State.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //使用打开一个连接
        public static string openConnection(string serverName,string userName,string pw)
        {
            string conStr = string.Format("Data Source={0}; Initial Catalog=master;User ID={1};Password={2}",serverName, userName, pw);
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conStr;
            try
            {
                conn.Open();
                return conn.State.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
            
            
        }

        //使用打开一个连接
        public static bool openConn(string connStr)
        {
            //string conStr = string.Format("Data Source={0}; Initial Catalog=master;User ID={1};Password={2}", serverName, userName, pw);
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connStr;
            bool result = false;
            try
            {
                conn.Open();
                result = (conn.State == ConnectionState.Open);
                conn.Close();
                conn.Dispose();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }



        }
        /// <summary>
        /// 返回莫一列的数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.Add(pms);
                    }
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        public static DataTable ExcuteQuery(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.Add(pms);
                    }
                    conn.Open();
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }
    }
}
